package com.happts.Utils;

import java.sql.*;

public class JDBCManager {
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://101.132.32.106:3306/SOFTWARE_PROJECT?characterEncoding=utf8";

    static final String USER = "guest0";
    static final String PASSWORD = "guest0";

    static {
        try {
            Class.forName(JDBC_DRIVER).newInstance();
        } catch (InstantiationException | ClassNotFoundException | IllegalAccessException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     */
    public static Connection getConnection() {

        Connection conn = null;

        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 关闭连接
     * @param con 连接
     * @param ps
     * @param rs
     */
    public static void close(Connection con, PreparedStatement ps, ResultSet rs){
        if(rs!=null)
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        if(ps!=null)
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        if(con!=null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }

    public static void close(Connection con,PreparedStatement ps){

        if(ps!=null)
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        if(con!=null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }

    public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 执行SQL查询语句
     * @param string SQL字符串
     * @return 结果集
     */
    public static ResultSet executeQuery(String string){
        Connection connection = getConnection();
        ResultSet resultSet = null;
        try {
            resultSet = connection.createStatement().executeQuery(string);
        }catch (SQLException e){
            System.out.println(string);
            e.printStackTrace();
        }
        return resultSet;
    }

    public static ResultSet executeQuery(String sql,Object[] params){
        Connection connection = getConnection();
        ResultSet resultSet = null;
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            int i;
            for(i=1;i<=params.length;i++){
                preparedStatement.setObject(i,params[i-1]);
            }
            resultSet = preparedStatement.executeQuery();
        }catch (SQLException e){
            System.out.println(sql);
            e.printStackTrace();
        }
        return resultSet;
    }

    /**
     * 执行SQL增删改
     * @param string SQL字符串
     * @return 执行结果
     */
    public static boolean executeSQL(String string){
        Connection connection = getConnection();
        boolean result =false;
        try{
           result = connection.createStatement().execute(string);
        }catch (SQLException e){
            System.out.println(string);
            e.printStackTrace();
        }
        return result;
    }

    public static boolean executeSQL(String sql,Object[] params){
        boolean result = false;
        try {
            Connection connection = JDBCManager.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            int i;
            for(i=1;i<=params.length;i++){
                preparedStatement.setObject(i,params[i-1]);
            }
            i = preparedStatement.executeUpdate();
            if(i==1)result = true;
        }catch (SQLException e){
            System.out.println(sql);
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 测试数据库连接是否成功
     * @param args
     */
    public static void main(String[] args) {
        System.out.println(JDBCManager.getConnection());
    }
}
